import os
import sqlite3
import json

# 获取 JSON 文件的路径（上一层目录）
# 获取当前文件所在目录
current_directory = os.path.dirname(os.path.abspath(__file__))

# 获取 JSON 文件的绝对路径（上一层目录）
json_file_path = os.path.abspath(os.path.join(current_directory, '..', 'Douban.json'))
# print(f"current_directory：{current_directory}，\nJSON file path: {json_file_path}")

# 读取 JSON 文件
with open(json_file_path, 'r', encoding='utf-8') as file:
    data = json.load(file)
print(f"data: {len(data)}")
db_file_path = os.path.abspath(os.path.join(current_directory, '../spiders', 'spider_database.db'))
print(db_file_path)
# 连接到 SQLite 数据库（如果数据库不存在，会自动创建）
conn = sqlite3.connect(db_file_path)
cursor = conn.cursor()

# 创建 movies 表（如果表不存在）
cursor.execute('''
    CREATE TABLE IF NOT EXISTS movies (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE,
        actors TEXT,
        aliases TEXT,
        comment_num TEXT,
        country TEXT,
        director TEXT,
        duration TEXT,
        genre TEXT,
        imdb TEXT,
        language TEXT,
        official_site TEXT,
        rating_num TEXT,
        release_dates TEXT,
        url TEXT,
        writer TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')
conn.commit()

# 插入数据
for item in data:
    try:
        cursor.execute('''
            INSERT INTO movies (name, actors, aliases, comment_num, country, director,
                                duration, genre, imdb, language, official_site,
                                rating_num, release_dates, url, writer)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            item['name'],
            ','.join(item['actors']),
            item['aliases'],
            item['comment_num'],
            item['country'],
            item['director'],
            ','.join(item['duration']),
            ','.join(item['genre']),
            item['imdb'],
            item['language'],
            item['official_site'],
            item['rating_num'],
            ','.join(item['release_dates']),
            item['url'],
            item['writer']
        ))
    except sqlite3.IntegrityError:
        print(f"Duplicate entry for {item['name']}")
    except Exception as e:
        print(f"Error inserting {item['name']}: {e}")

# 提交并关闭数据库连接
conn.commit()
conn.close()

print("Data imported successfully.")
